﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using HospitalInfomationSystem.Model;
using System.Data;
using System.Data.SqlClient;
using Dapper;

namespace HospitalInfomationSystem.DAL
{
    /*
     * 创建者：哈希君
     * 时间：2017.05.27
     */

    //https://github.com/StackExchange/Dapper

    //http://www.cnblogs.com/huangxincheng/p/5832281.html
    //http://www.cnblogs.com/Sinte-Beuve/p/4231053.html

    /// <summary>
    /// 提供对Person表的数据库访问，
    /// 此为基本用法示例
    /// </summary>
    public class PersonDB
    {
        /// <summary>
        /// 数据库连接字符串，由app.config文件读取
        /// </summary>
        private static readonly string connectionString = System.Configuration.ConfigurationManager.AppSettings["connectionString"];

        #region 插入

        /// <summary>
        /// 插入一个Person数据，返回影响行数
        /// </summary>
        /// <param name="person"></param>
        /// <returns>影响行数</returns>
        public static int Insert(Person person)
        {
            using (IDbConnection connection = new SqlConnection(connectionString))
            {
                return connection.Execute("insert into Person(Name,Remark) values(@Name,@Remark)", person);
            }
        }

        /// <summary>
        /// 批量插入Person数据，返回影响行数
        /// </summary>
        /// <param name="persons"></param>
        /// <returns>影响行数</returns>
        public static int Insert(List<Person> persons)
        {
            using (IDbConnection connection = new SqlConnection(connectionString))
            {
                return connection.Execute("insert into Person(Name,Remark) values(@Name,@Remark)", persons);
            }
        }

        #endregion

        #region 删除

        public static int Delete(Person person)
        {
            using (IDbConnection connection = new SqlConnection(connectionString))
            {
                return connection.Execute("delete from Person where id=@ID", person);
            }
        }

        public static int Delete(List<Person> persons)
        {
            using (IDbConnection connection = new SqlConnection(connectionString))
            {
                return connection.Execute("delete from Person where id=@ID", persons);
            }
        }

        //示例方法
        public static int DeleteWithNameAndRemark(Person person)
        {
            using (IDbConnection connection = new SqlConnection(connectionString))
            {
                return connection.Execute("delete from Person where remark=@Remark and name=@Name", person);
            }
        }

        #endregion

        #region 修改

        public static int Update(Person person)
        {
            using (IDbConnection connection = new SqlConnection(connectionString))
            {
                return connection.Execute("update Person set name=@name where id=@ID", person);
            }
        }

        public static int Update(List<Person> persons)
        {
            using (IDbConnection connection = new SqlConnection(connectionString))
            {
                return connection.Execute("update Person set name=@name where id=@ID", persons);
            }
        }

        #endregion

        #region 查询

        /// <summary>
        /// 无参查询所有数据
        /// </summary>
        /// <returns></returns>
        public static List<Person> Query()
        {
            using (IDbConnection connection = new SqlConnection(connectionString))
            {
                return connection.Query<Person>("select * from Person").ToList();
            }
        }

        /// <summary>
        /// 查询指定数据
        /// </summary>
        /// <param name="person"></param>
        /// <returns></returns>
        public static Person Query(Person person)
        {
            using (IDbConnection connection = new SqlConnection(connectionString))
            {
                return connection.Query<Person>("select * from Person where id=@ID", person).SingleOrDefault();
            }
        }

        /// <summary>
        /// In操作
        /// </summary>
        public static List<Person> QueryIn()
        {
            using (IDbConnection connection = new SqlConnection(connectionString))
            {
                var sql = "select * from Person where id in @ids";
                //参数类型是Array的时候，dappper会自动将其转化
                return connection.Query<Person>(sql, new { ids = new int[2] { 1, 2 }, }).ToList();
            }
        }

        public static List<Person> QueryIn(int[] ids)
        {
            using (IDbConnection connection = new SqlConnection(connectionString))
            {
                var sql = "select * from Person where id in @ids";
                //参数类型是Array的时候，dappper会自动将其转化
                return connection.Query<Person>(sql, new { ids }).ToList();
            }
        }

        /// <summary>
        /// 多语句操作
        /// </summary>
        public static void QueryMultiple()
        {
            using (IDbConnection connection = new SqlConnection(connectionString))
            {
                var sql = "select * from Person; select * from Book";
                var multiReader = connection.QueryMultiple(sql);
                var productList = multiReader.Read<Person>();
                var userList = multiReader.Read<Book>();
                multiReader.Dispose();
            }
        }

        public static BookWithPerson QueryJoin(Book book)
        {
            using (IDbConnection connection = new SqlConnection(connectionString))
            {
                var sql = @"select b.id,b.bookName,p.id,p.name,p.remark
                            from Person as p
                            join Book as b
                            on p.id = b.personId
                            where b.id = @id;";
                var result = connection.Query<BookWithPerson, Person, BookWithPerson>(sql,
                    (bookWithPerson, person) =>
                    {
                        bookWithPerson.Pers = person;
                        return bookWithPerson;
                    },
                    book);
                    //splitOn: "bookName");
                return (BookWithPerson)result;
            }
        }

        #endregion
    }
}
